package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.Anwesenheit;
import Dao.ConnectionProvider;
import Dao.Dao_Lernbuero;
import Dao.Dao_Schueler;

public class Dao_Anwesenheit 
{
	private Dao_Lernbuero lernbueroDAO=new  Dao_Lernbuero();
	private Dao_Schueler  schuelerDAO= new Dao_Schueler();
	
	public Anwesenheit get(int id)
	{
		Anwesenheit a=null;
		Connection conn=ConnectionProvider.getConnection();
		PreparedStatement statement=null;
		ResultSet rs=null;
		String sql="select * from anwesenheit where anwesenheitid=?";
        try {
			statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			rs=statement.executeQuery();
			
			if (rs.next())
			{
				a=new Anwesenheit();
				a.setAnwesenheitID(id);
				a.setKanlederwoche(rs.getString("kw"));
				a.setSchuljahr(rs.getString("aschuljahr"));
				a.setSchueler_ID(schuelerDAO.get(rs.getInt("schuelerID")));
				a.setLbID(lernbueroDAO.getLbID(rs.getInt("lbid")));
				a.setWochentag(rs.getString("wochentag"));
			}
			
			return a;
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				rs.close();
				statement.close();
				conn.close();
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public void update(Anwesenheit a)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="update anwesenheit set aschuljahr=?, kw=?, wochentag=?, schuelerID=?, lbid=? where anwesenheitid=?";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setString(1, a.getSchuljahr());
			statement.setString(2, a.getKanlederwoche());
			statement.setString(3, a.getWochentag());
			statement.setInt(4, a.getSchueler_ID().getSchuelerID());
			statement.setInt(5, a.getLbID().getLbID());
			statement.setInt(6, a.getAnwesenheitID());

			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void anlegen(Anwesenheit a)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="insert into Anwesenheit VALUES (?, ?, ?, ?, ?, ?)";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, a.getAnwesenheitID());
			statement.setString(2, a.getSchuljahr());
			statement.setString(3, a.getKalenderwoche());
			statement.setString(4, a.getWochentag());
			statement.setInt(5, a.getSchueler_ID().getSchuelerID());
			statement.setInt(6, a.getLbID().getLbID());

			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void delete(int id)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="delete from anwesenheit where anwesenheitid= ?";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			statement.executeUpdate();
        } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
        finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}


	

}
